using System;
using System.Data;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace Data_Access_Tier
{
    /// <summary>
    /// Summary description for AuditDatabaseData
    /// </summary>
    public class AuditDatabaseData
    {
        private SqlDataAdapter _dbadapter;
        private DataSet _data;
        private SqlConnection _dbconn;

        public AuditDatabaseData()
        {
            try
            {
                //Build the adapter commands.
                _dbadapter = new SqlDataAdapter();
                _data = new DataSet();
            }
            catch (SqlException e)
            {
                throw new Exception("AuditDatabaseData object Construction error:", e);
            }
        }

        public Int32 GetDataSetCount()
        {
            if (_data != null)
                if (_data.Tables.Count > 0)
                    return _data.Tables[0].Rows.Count;

            return 0;
        }

        public Int32 AuditDatabase_Insert(Int32 Users_PKID, String AuditGroup, String TableName, String EventType, String BeforeData, String AfterData, SqlTransaction trans)
        {
            try
            {
                if (trans == null)
                    return 0;

                _dbadapter.InsertCommand = new SqlCommand("AuditDatabase_Insert");
                _dbadapter.InsertCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.InsertCommand.Connection = trans.Connection;
                _dbadapter.InsertCommand.Transaction = trans;
                _dbadapter.InsertCommand.Parameters.Clear();
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@intUsers_PKID", SqlDbType.Int));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@varcharAuditGroup", SqlDbType.VarChar, 20));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@varcharTableName", SqlDbType.VarChar, 100));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@varcharEventType", SqlDbType.VarChar, 100));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@varcharBeforeData", SqlDbType.VarChar, 3000));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@varcharAfterData", SqlDbType.VarChar, 3000));
                _dbadapter.InsertCommand.Prepare();
                _dbadapter.InsertCommand.Parameters["@intUsers_PKID"].Value = Users_PKID;
                SqlString AuditGroupString = AuditGroup;
                _dbadapter.InsertCommand.Parameters["@varcharAuditGroup"].Value = AuditGroupString;
                SqlString TableNameString = TableName;
                _dbadapter.InsertCommand.Parameters["@varcharTableName"].Value = TableNameString;
                SqlString EventTypeString = EventType;
                _dbadapter.InsertCommand.Parameters["@varcharEventType"].Value = EventTypeString;
                SqlString BeforeDataString = BeforeData;
                _dbadapter.InsertCommand.Parameters["@varcharBeforeData"].Value = BeforeDataString;
                SqlString AfterDataString = AfterData;
                _dbadapter.InsertCommand.Parameters["@varcharAfterData"].Value = AfterDataString;
                Int32 returnvalue = Convert.ToInt32(_dbadapter.InsertCommand.ExecuteScalar());

                return returnvalue;
            }
            catch (SqlException e)
            {
                try
                {
                    trans.Rollback();
                }
                catch (SqlException ex)
                {
                    throw new Exception("SQL Rollback Error: ", ex);
                }
                throw new Exception("SQL Error: ", e);
            }
            finally
            {
                if (_dbadapter.InsertCommand != null)
                    _dbadapter.InsertCommand.Dispose();
            }
        }

        public DataSet AuditDatabase_GetBySingleRow(Int32 PKID)
        {
            try
            {
                _dbconn = new SqlConnection();
                AppSettingsReader appReader = new AppSettingsReader();

                _dbconn.ConnectionString = (String)appReader.GetValue("DBConnectionString", typeof(String));

                _dbadapter.SelectCommand = new SqlCommand("AuditDatabase_GetBySingleRow");
                _dbadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.SelectCommand.Connection = _dbconn;
                _dbadapter.SelectCommand.Parameters.Clear();
                _dbadapter.SelectCommand.Parameters.Add(new SqlParameter("@intPKID", SqlDbType.Int));
                _dbadapter.SelectCommand.Prepare();
                _dbadapter.SelectCommand.Parameters["@intPKID"].Value = PKID;
                _dbadapter.Fill(_data);
                return _data;
            }
            catch (SqlException e)
            {
                throw new Exception("SQL Error: ", e);
            }
            finally
            {
                if (_dbadapter.SelectCommand != null)
                    _dbadapter.SelectCommand.Dispose();
                _dbconn.Close();
            }
        }

        public DataSet AuditDatabase_GetList()
        {
            try
            {
                _dbconn = new SqlConnection();
                AppSettingsReader appReader = new AppSettingsReader();

                _dbconn.ConnectionString = (String)appReader.GetValue("DBConnectionString", typeof(String));

                _dbadapter.SelectCommand = new SqlCommand("AuditDatabase_GetList");
                _dbadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.SelectCommand.Connection = _dbconn;
                _dbadapter.SelectCommand.Parameters.Clear();
                _dbadapter.SelectCommand.Prepare();
                _dbadapter.Fill(_data);
                return _data;
            }
            catch (SqlException e)
            {
                throw new Exception("SQL Error: ", e);
            }
            finally
            {
                if (_dbadapter.SelectCommand != null)
                    _dbadapter.SelectCommand.Dispose();
                _dbconn.Close();
            }
        }

    }
}